package com.pomelo.base.core.dao;

import java.io.ByteArrayOutputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
import org.springframework.stereotype.Component;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionException;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import org.springframework.transaction.support.DefaultTransactionStatus;
import org.springframework.util.LinkedCaseInsensitiveMap;
import org.springframework.util.ObjectUtils;

import com.github.pagehelper.PageRowBounds;
import com.pomelo.base.core.bean.ColumnMetaData;
import com.pomelo.base.core.bean.DbTypeEnum;
import com.pomelo.base.core.bean.TableIndexInfo;
import com.pomelo.base.core.bean.TableMetaData;
import com.pomelo.base.core.config.MultiDataSourceUtil;
import com.pomelo.base.core.config.PomeloTransactionStatus;
import com.pomelo.base.core.datasource.DynamicDataSourceContextHolder;
import com.pomelo.base.core.util.DbUtil;
import com.pomelo.base.core.util.MybatisUtil;
import com.pomelo.base.core.util.SpringUtil;

import cn.hutool.core.util.IdUtil;

/**
 * 操作数据库service,通过JdbcTemplate操作数据库
 *
 * @author biankudingcha
 * @date: 2021-07-23
 * @since 0.5.0
 */
@Component
public class DaoService {

    /**
     * 日志对象
     */
    private static final Log LOGGER = LogFactory.getLog(DaoService.class);

    /**
     * 数据库类型
     */
    @Value("${spring.datasource.druid.dbType}")
    private String dbType;

    /**
     * 默认JdbcTemplate
     */
    @Autowired
    @Qualifier("JdbcTemplate-defaultDataSource")
    private JdbcTemplate jdbcTemplate;

    /**
     * 事务管理器
     */
    @Autowired
    @Qualifier("PlatformTransactionManager-defaultDataSource")
    private PlatformTransactionManager transactionManager;

    public DaoService() {

    }

    /**
     * 获取事务管理
     *
     * @return PlatformTransactionManager
     * @throws Exception
     */
    public PlatformTransactionManager getPlatformTransactionManager() throws Exception {
        return getPlatformTransactionManager(null);
    }

    /**
     * 获取事务管理
     *
     * @param dataSourceId 数据源id
     * @return PlatformTransactionManager
     * @throws Exception
     */
    public PlatformTransactionManager getPlatformTransactionManager(String dataSourceId) throws Exception {
        if (dataSourceId == null || "".equals(dataSourceId)) {
            dataSourceId = DynamicDataSourceContextHolder.getDataSourceId();
        }
        if (dataSourceId == null || "".equals(dataSourceId)) {
            return this.transactionManager;
        } else {
            if (!DynamicDataSourceContextHolder.containsDataSourceId(dataSourceId)) {
                LOGGER.info("不存在数据源[" + dataSourceId + "]");
                return this.transactionManager;
            } else {
                return SpringUtil.getBeanByName(PlatformTransactionManager.class,
                    "PlatformTransactionManager-" + dataSourceId);
            }
        }
    }

    /**
     * 获取JdbcTemplate操作类
     *
     * @return JdbcTemplate
     * @throws Exception
     */
    public JdbcTemplate getJdbcTemplate() throws Exception {
        return getJdbcTemplate(null);
    }

    /**
     * 获取JdbcTemplate操作类
     *
     * @param dataSourceId 数据源
     * @return JdbcTemplate
     * @throws Exception
     */
    public JdbcTemplate getJdbcTemplate(String dataSourceId) throws Exception {
        if (dataSourceId == null || "".equals(dataSourceId)) {
            dataSourceId = DynamicDataSourceContextHolder.getDataSourceId();
        }
        if (dataSourceId == null || "".equals(dataSourceId)) {
            LOGGER.debug("当前使用的数据源:默认数据源");
            return this.jdbcTemplate;
        } else {
            if (!DynamicDataSourceContextHolder.containsDataSourceId(dataSourceId)) {
                LOGGER.info("不存在数据源[" + dataSourceId + "]");
                LOGGER.debug("当前使用的数据源:默认数据源");
                return this.jdbcTemplate;
            } else {
                LOGGER.debug("当前使用的数据源:" + dataSourceId);
                return SpringUtil.getBeanByName(JdbcTemplate.class, "JdbcTemplate-" + dataSourceId);
            }
        }
    }

    /**
     * 生成插入SQL语句
     *
     * @param tableName 表名称
     * @param data 数据
     * @return SqlWrapper
     * @throws Exception
     */
    public SqlWrapper getInsertSql(String tableName, Map<String, Object> data) throws Exception {
        StringBuilder sqlBuf = new StringBuilder();
        List<Object> params = new ArrayList<Object>();
        sqlBuf.append("INSERT INTO ").append(tableName).append("(");
        int index = 0;
        for (Iterator<String> iter = data.keySet().iterator(); iter.hasNext();) {
            String key = iter.next();
            Object value = data.get(key);
            if (index == 0) {
                sqlBuf.append(key);
            } else {
                sqlBuf.append(",").append(key);
            }
            params.add(value);
            index++;
        }
        sqlBuf.append(") VALUES (");
        sqlBuf.append(getSqlPlaceholder(params));
        sqlBuf.append(")");
        return new SqlWrapper(sqlBuf.toString(), params);
    }

    /**
     * 生成更新SQL语句
     *
     * @param tableName 表名称
     * @param keyNames 主键
     * @param data 数据
     * @param condition 条件
     * @param param 参数
     * @return SqlWrapper
     * @throws Exception
     */
    public SqlWrapper getUpdateSql(String tableName, String[] keyNames, Map<String, Object> data, String condition,
        Object[] param) throws Exception {
        if (keyNames == null || keyNames.length < 1) {
            keyNames = new String[] { "WID" };
        }
        StringBuilder sqlBuf = new StringBuilder();
        List<Object> params = new ArrayList<Object>();
        sqlBuf.append("UPDATE ").append(tableName).append(" SET ");
        int index = 0;
        for (Iterator<String> iter = data.keySet().iterator(); iter.hasNext();) {
            boolean isContinue = false;
            String key = iter.next();
            for (int i = 0; i < keyNames.length; i++) {
                if (keyNames[i].equalsIgnoreCase(key)) {
                    isContinue = true;
                    break;
                }
            }
            if (isContinue) {
                continue;
            }
            Object value = data.get(key);
            if (index == 0) {
                sqlBuf.append(key).append(" = ?");
            } else {
                sqlBuf.append(",").append(key).append(" = ?");
            }
            params.add(value);
            index++;
        }
        // 添加数据过滤条件
        sqlBuf.append(" WHERE ");
        for (int i = 0, len = keyNames.length; i < len; i++) {
            if (i == 0) {
                sqlBuf.append(keyNames[i]).append(" = ? ");
            } else {
                sqlBuf.append(" AND ").append(keyNames[i]).append(" = ? ");
            }
            params.add(data.get(keyNames[i]));
        }
        if (condition != null && condition.length() > 0) {
            sqlBuf.append(" AND ").append(condition);
            if (param != null && param.length > 0) {
                Collections.addAll(params, param);
            }
        }
        return new SqlWrapper(sqlBuf.toString(), params);
    }

    /**
     * 更新SQL语句
     *
     * @param tableName 表名称
     * @param data 数据
     * @param condition 条件
     * @param params 参数
     * @return SqlWrapper
     * @throws Exception
     */
    public SqlWrapper getUpdateSql(String tableName, Map<String, Object> data, String condition, Object[] params)
        throws Exception {
        return getUpdateSql(tableName, null, data, condition, params);
    }

    /**
     * 保存
     *
     * @param dataSourceId 数据源
     * @param tableName 表名称
     * @param data 数据
     * @return String
     * @throws Exception
     */
    public String save(String dataSourceId, String tableName, Map<String, Object> data) throws Exception {
        if (data == null || data.isEmpty()) {
            return null;
        }
        SqlWrapper sw = getInsertSql(tableName, data);
        logSql(sw);
        getJdbcTemplate(dataSourceId).update(sw.getSql(), sw.getParams().toArray());
        return (String) data.get("WID");
    }

    /**
     * 更新
     *
     * @param dataSourceId 数据源
     * @param tableName 表名称
     * @param data 数据
     * @param condition 过滤条件
     * @param params 参数
     * @return String
     * @throws Exception
     */
    public String update(String dataSourceId, String tableName, Map<String, Object> data, String condition,
        Object[] params) throws Exception {
        if (data == null || data.isEmpty()) {
            return null;
        }
        SqlWrapper sw = getUpdateSql(tableName, data, condition, params);
        logSql(sw);
        getJdbcTemplate(dataSourceId).update(sw.getSql(), sw.getParams().toArray());
        return (String) data.get("WID");
    }

    /**
     * 更新
     *
     * @param dataSourceId 数据源
     * @param tableName 表名称
     * @param keyNames 主键
     * @param data 数据
     * @param condition 过滤条件
     * @param params 参数
     * @return String
     * @throws Exception
     */
    public String update(String dataSourceId, String tableName, String[] keyNames, Map<String, Object> data,
        String condition, Object[] params) throws Exception {
        if (data == null || data.isEmpty()) {
            return null;
        }
        SqlWrapper sw = getUpdateSql(tableName, keyNames, data, condition, params);
        logSql(sw);
        getJdbcTemplate(dataSourceId).update(sw.getSql(), sw.getParams().toArray());
        return (String) data.get("WID");
    }

    /**
     * 保存或者更新，如果WID有值，则执行更新操作，否则执行报错操作
     *
     * @param dataSourceId 数据源
     * @param tableName 表名称
     * @param data 数据
     * @param condition 条件
     * @param params 参数
     * @return String
     * @throws Exception
     */
    public String saveOrUpdate(String dataSourceId, String tableName, Map<String, Object> data, String condition,
        Object[] params) throws Exception {
        if (data == null || data.isEmpty()) {
            return null;
        }
        Object wid = data.get("WID");
        if (ObjectUtils.isEmpty(wid)) {
            data.put("WID", getNewWid());
            return save(dataSourceId, tableName, data);
        } else {
            return update(dataSourceId, tableName, data, condition, params);
        }
    }

    /**
     * 批量更新或者插入
     *
     * @param dataSourceId 数据源
     * @param tableName 表名称
     * @param datas 数据
     * @param condition 条件
     * @param params 参数
     * @throws Exception
     */
    public void saveOrUpdate(String dataSourceId, String tableName, List<Map<String, Object>> datas, String condition,
        Object[] params) throws Exception {
        if (datas == null || datas.isEmpty()) {
            return;
        }
        List<Map<String, Object>> saveList = new ArrayList<Map<String, Object>>();
        List<Map<String, Object>> updateList = new ArrayList<Map<String, Object>>();
        for (Map<String, Object> data : datas) {
            Object wid = data.get("WID");
            if (ObjectUtils.isEmpty(wid)) {
                data.put("WID", getNewWid());
                saveList.add(data);
            } else {
                updateList.add(data);
            }
        }
        if (!saveList.isEmpty()) {
            save(dataSourceId, tableName, saveList);
        }
        if (!updateList.isEmpty()) {
            update(dataSourceId, tableName, updateList, condition, params);
        }
    }

    /**
     * 批量保存
     *
     * @param dataSourceId 数据源
     * @param tableName 表名称
     * @param datas 数据
     * @throws Exception
     */
    public void save(String dataSourceId, String tableName, List<Map<String, Object>> datas) throws Exception {
        if (datas == null || datas.isEmpty()) {
            return;
        }
        checkListElementEqual(datas);
        StringBuilder sb = new StringBuilder("INSERT INTO ").append(tableName).append(" ( ");
        Map<String, Object> data = datas.get(0);
        int index = 0;
        final List<String> keys = new ArrayList<String>();
        for (Iterator<Entry<String, Object>> iter = data.entrySet().iterator(); iter.hasNext();) {
            Entry<String, Object> entry = iter.next();
            if (index == 0) {
                sb.append(entry.getKey());
            } else {
                sb.append(",").append(entry.getKey());
            }
            keys.add(entry.getKey());
            index++;
        }
        sb.append(")").append(" VALUES (").append(getSqlPlaceholder(keys)).append(")");
        String sql = sb.toString();
        getJdbcTemplate(dataSourceId).batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public int getBatchSize() {
                // 这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
                return datas.size();
            }

            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Map<String, Object> data = datas.get(i);
                int parameterIndex = 0;
                for (String key : keys) {
                    Object object = data.get(key);
                    if (object instanceof String) {
                        ps.setString(++parameterIndex, (String) object);
                    } else if (object instanceof Integer) {
                        ps.setInt(++parameterIndex, (Integer) object);
                    } else if (object instanceof Date) {
                        Date date = (Date) object;
                        ps.setDate(++parameterIndex, new java.sql.Date(date.getTime()));
                    } else if (object instanceof byte[]) {
                        ps.setBytes(++parameterIndex, (byte[]) object);
                    } else {
                        ps.setObject(++parameterIndex, object);
                    }
                }
            }

        });
    }

    /**
     * 批量更新
     *
     * @param dataSourceId 数据源
     * @param tableName 表名称
     * @param datas 数据
     * @param condition 条件
     * @param params 参数
     * @throws Exception
     */
    public void update(String dataSourceId, String tableName, List<Map<String, Object>> datas, String condition,
        Object[] params) throws Exception {
        if (datas == null || datas.isEmpty()) {
            return;
        }
        checkListElementEqual(datas);
        StringBuilder sb = new StringBuilder("UPDATE ").append(tableName).append(" SET ");
        Map<String, Object> data = datas.get(0);
        int index = 0;
        final List<String> keys = new ArrayList<String>();
        for (Iterator<Entry<String, Object>> iter = data.entrySet().iterator(); iter.hasNext();) {
            Entry<String, Object> entry = iter.next();
            String key = entry.getKey();
            keys.add(key);
            if (index == 0) {
                sb.append(key).append(" = ?");
            } else {
                sb.append(",").append(key).append(" = ?");
            }
            index++;
        }
        sb.append(" WHERE WID = ?");
        String sql = sb.toString();
        getJdbcTemplate(dataSourceId).batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public int getBatchSize() {
                // 这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
                return datas.size();
            }

            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Map<String, Object> data = datas.get(i);
                int parameterIndex = 0;
                for (String key : keys) {
                    Object object = data.get(key);
                    if (object instanceof String) {
                        ps.setString(++parameterIndex, (String) object);
                    } else if (object instanceof Integer) {
                        ps.setInt(++parameterIndex, (Integer) object);
                    } else if (object instanceof Date) {
                        Date date = (Date) object;
                        ps.setDate(++parameterIndex, new java.sql.Date(date.getTime()));
                    } else if (object instanceof byte[]) {
                        ps.setBytes(++parameterIndex, (byte[]) object);
                    } else {
                        ps.setObject(++parameterIndex, object);
                    }
                }
                ps.setObject(++parameterIndex, data.get("WID"));
            }
        });
    }

    /**
     * 根据WID删除数据
     *
     * @param dataSourceId 数据源
     * @param tableName 表名称
     * @param wid 主键值
     * @param condition 条件
     * @param params 参数
     * @return int
     * @throws Exception
     */
    public int deleteByWid(String dataSourceId, String tableName, String wid, String condition, Object[] params)
        throws Exception {
        String sql = "DELETE FROM " + tableName + " WHERE WID = ?";
        if (!ObjectUtils.isEmpty(condition)) {
            sql += " AND " + condition;
        }
        List<Object> paramList = new ArrayList<Object>();
        paramList.add(wid);
        if (params != null && params.length > 0) {
            Collections.addAll(paramList, params);
        }
        int result = getJdbcTemplate(dataSourceId).update(sql, paramList.toArray());
        return result;
    }

    /**
     * 删除数据
     *
     * @param dataSourceId 数据源
     * @param tableName 表名称
     * @param condition 条件
     * @param params 参数
     * @return int
     * @throws Exception
     */
    public int delete(String dataSourceId, String tableName, String condition, Object[] params) throws Exception {
        String sql = "DELETE FROM " + tableName;
        if (!ObjectUtils.isEmpty(condition)) {
            sql += " WHERE " + condition;
        }
        int result = getJdbcTemplate(dataSourceId).update(sql, params);
        return result;
    }

    /**
     * 执行SQL语句
     *
     * @param dataSourceId 数据源
     * @param sql 执行SQL
     * @param params 参数
     * @return int
     * @throws Exception
     */
    public int execute(String dataSourceId, String sql, Object[] params) throws Exception {
        int result = getJdbcTemplate(dataSourceId).update(sql, params);
        return result;
    }

    /**
     * 执行SQL语句
     *
     * @param dataSourceId 数据源
     * @param sql 执行SQL
     * @return int
     * @throws Exception
     */
    public void execute(String dataSourceId, String sql) throws Exception {
       getJdbcTemplate(dataSourceId).execute(sql);
    }

    /**
     * 查询
     *
     * @param dataSourceId 数据源
     * @param sql 查询SQL
     * @param params 参数
     * @return List
     * @throws Exception
     */
    public List<Map<String, Object>> query(String dataSourceId, String sql, Object[] params) throws Exception {
        try {
            if (params == null) {
                params = new Object[] {};
            }
            List<Map<String, Object>> datas = getJdbcTemplate(dataSourceId).query(sql, new PomeloColumnMapRowMapper(),
                params);
            if (datas == null || datas.isEmpty()) {
                return new ArrayList<Map<String, Object>>();
            }
            changeType(datas);
            return datas;
        } finally {
        }
    }

    /**
     * 查询
     *
     * @param dataSourceId 数据源
     * @param sql 查询SQL
     * @param params 参数
     * @param page 分页信息
     * @param sort 排序字段
     * @return List
     * @throws Exception
     */
    public List<Map<String, Object>> query(String dataSourceId, String sql, Object[] params, PageRowBounds page,
        String sort) throws Exception {
        if (params == null) {
            params = new Object[] {};
        }
        if (!ObjectUtils.isEmpty(sort)) {
            sql = sql + " ORDER BY " + convertSort(sort);
        }
        // 此处分页，使用Mybatis
        List<Map<String, Object>> datas = MybatisUtil.executeQuerySql(dataSourceId, sql, params, page);
        if (datas == null || datas.isEmpty()) {
            return new ArrayList<Map<String, Object>>();
        }
        return datas;
    }

    /**
     * 转换排序
     *
     * @param sort 排序字段
     * @return String
     * @throws Exception
     */
    private String convertSort(String sort) throws Exception {
        if (ObjectUtils.isEmpty(sort)) {
            return " WID ";
        } else {
            String[] fields = sort.split(",");
            List<String> sortFieldList = new ArrayList<String>();
            for (int i = 0, len = fields.length; i < len; i++) {
                String field = fields[i];
                if (field.startsWith("-")) {
                    sortFieldList.add(field.substring(1) + " DESC ");
                } else if (field.startsWith("+")) {
                    sortFieldList.add(field.substring(1) + " ASC ");
                } else {
                    sortFieldList.add(field);
                }
            }
            return org.apache.commons.lang3.StringUtils.join(sortFieldList, ",");
        }
    }

    /**
     * 查询数据
     *
     * @param dataSourceId 数据源
     * @param sql 查询SQL
     * @param params 参数
     * @param page 分页信息
     * @return List
     * @throws Exception
     */
    public List<Map<String, Object>> query(String dataSourceId, String sql, Object[] params, PageRowBounds page)
        throws Exception {
        List<Map<String, Object>> datas = query(dataSourceId, sql, params, page, null);
        if (datas == null || datas.isEmpty()) {
            return new ArrayList<Map<String, Object>>();
        }
        return datas;
    }

    /**
     * 查询一行数据
     *
     * @param dataSourceId 数据源
     * @param sql 查询SQL
     * @param params 参数
     * @return Map
     * @throws Exception
     */
    public Map<String, Object> queryRow(String dataSourceId, String sql, Object[] params) throws Exception {
        List<Map<String, Object>> datas = query(dataSourceId, sql, params);
        if (datas == null || datas.isEmpty()) {
            return new HashMap<String, Object>(0);
        }
        return datas.get(0);
    }

    /**
     * 查询一行一列数据
     *
     * @param dataSourceId 数据源
     * @param sql 查询SQL
     * @param params 参数
     * @return Object
     * @throws Exception
     */
    public Object queryOne(String dataSourceId, String sql, Object[] params) throws Exception {
        List<Object> result = queryOnes(dataSourceId, sql, params);
        if (result == null || result.isEmpty()) {
            return null;
        }
        return result.get(0);
    }

    /**
     * 查询SQL中第一个字段值
     *
     * @param dataSourceId 数据源
     * @param sql 查询SQL
     * @param params 参数
     * @return List
     * @throws Exception
     */
    public List<Object> queryOnes(String dataSourceId, String sql, Object[] params) throws Exception {
        final List<Object> result = new ArrayList<Object>();
        getJdbcTemplate(dataSourceId).query(sql, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet resultSet) throws SQLException {
                result.add(resultSet.getObject(1));
            }
        }, params);
        return result;
    }

    /**
     * 查询多行返回一个map,第一个字段值为键，第二个字段值为值
     *
     * @param dataSourceId 数据源
     * @param sql 查询SQL
     * @param params 参数
     * @return Map
     * @throws Exception
     */
    public Map<String, Object> queryMap(String dataSourceId, String sql, Object[] params) throws Exception {
        final Map<String, Object> result = new HashMap<String, Object>();
        getJdbcTemplate(dataSourceId).query(sql, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet resultSet) throws SQLException {
                result.put(resultSet.getString(1), resultSet.getObject(2));
            }
        }, params);
        return result;
    }

    /**
     * 查询行记录转为map,取第一个字段为key,第二个字段为value
     *
     * @param dataSourceId 数据源
     * @param sql 查询SQL
     * @param params 参数
     * @return Map
     * @throws Exception
     */
    public Map<String, Object> queryRowForMap(String dataSourceId, String sql, Object[] params) throws Exception {
        final Map<String, Object> result = new LinkedHashMap<String, Object>();
        getJdbcTemplate(dataSourceId).query(sql, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet resultSet) throws SQLException {
                result.put(resultSet.getString(1), resultSet.getObject(2));
            }
        }, params);
        return result;
    }

    /**
     * 结果转int
     *
     * @param dataSourceId 数据源
     * @param sql 查询SQL
     * @param params 参数
     * @return int
     * @throws Exception
     */
    public int queryParseInt(String dataSourceId, String sql, Object[] params) throws Exception {
        Object obj = queryOne(dataSourceId, sql, params);
        if (obj == null) {
            return 0;
        }
        return Integer.parseInt(obj.toString());
    }

    /**
     * 根据表查询数据
     *
     * @param dataSourceId 数据源 
     * @param tablename 表名称
     * @param condition 条件
     * @param params 参数
     * @return List
     * @throws Exception
     */
    public List<Map<String, Object>> queryTable(String dataSourceId, String tablename, String condition,
        Object[] params) throws Exception {
        return queryTable(dataSourceId, tablename, condition, params, null);
    }

    /**
     * 查询数据，有排序
     *
     * @param dataSourceId 数据源  
     * @param tablename 表名称
     * @param condition 条件
     * @param params 参数
     * @param sort 排序
     * @return List
     * @throws Exception
     */
    public List<Map<String, Object>> queryTable(String dataSourceId, String tablename, String condition,
        Object[] params, String sort) throws Exception {
        String sql = "SELECT * FROM " + tablename;
        if (!ObjectUtils.isEmpty(condition)) {
            sql += " WHERE " + condition;
        }
        if (!ObjectUtils.isEmpty(sort)) {
            sql += " ORDER BY " + convertSort(sort);
        }
        return query(dataSourceId, sql, params);
    }

    /**
     * 根据wid查询
     *
     * @param dataSourceId 数据源   
     * @param tablename 表名称
     * @param wid 主键
     * @return Map
     * @throws Exception
     */
    public Map<String, Object> queryTableByWid(String dataSourceId, String tablename, String wid) throws Exception {
        String sql = "SELECT * FROM " + tablename + " WHERE WID = ?";
        return queryRow(dataSourceId, sql, new Object[] { wid });
    }

    /**
     * 查询一条记录
     *
     * @param dataSourceId 数据源
     * @param tablename 表名称
     * @param condition 条件
     * @param params 参数
     * @return Map
     * @throws Exception
     */
    public Map<String, Object> queryTableRow(String dataSourceId, String tablename, String condition, Object[] params)
        throws Exception {
        List<Map<String, Object>> datas = queryTable(dataSourceId, tablename, condition, params);
        if (datas == null || datas.isEmpty()) {
            return new HashMap<String, Object>(0);
        }
        return datas.get(0);
    }

    /**
     * 分页查询
     *
     * @param dataSourceId 数据源
     * @param tablename 表名称
     * @param page 分页信息
     * @param condition 条件
     * @param params 参数
     * @return List
     * @throws Exception
     */
    public List<Map<String, Object>> queryTable(String dataSourceId, String tablename, PageRowBounds page,
        String condition, Object[] params) throws Exception {
        List<Map<String, Object>> datas = queryTable(dataSourceId, tablename, page, null, condition, params);
        if (datas == null || datas.isEmpty()) {
            return new ArrayList<Map<String, Object>>();
        }
        return datas;
    }

    /**
     * 分页查询
     *
     * @param dataSourceId 数据源
     * @param tablename 表名称
     * @param page 分页
     * @param ordery 排序
     * @param condition 条件
     * @param params 参数
     * @return List
     * @throws Exception
     */
    public List<Map<String, Object>> queryTable(String dataSourceId, String tablename, PageRowBounds page,
        String ordery, String condition, Object[] params) throws Exception {
        String sql = "SELECT * FROM " + tablename;
        if (!ObjectUtils.isEmpty(condition)) {
            sql += " WHERE " + condition;
        }
        List<Map<String, Object>> datas = query(dataSourceId, sql, params, page, ordery);
        if (datas == null || datas.isEmpty()) {
            return new ArrayList<Map<String, Object>>();
        }
        return datas;
    }

    /**
     * 根据表，查询总记录
     *
     * @param dataSourceId 数据源
     * @param tablename 表名称
     * @param condition 条件
     * @param params 参数
     * @return long
     * @throws Exception
     */
    public long queryTableCount(String dataSourceId, String tablename, String condition, Object[] params)
        throws Exception {
        String sql = "SELECT COUNT(*) FROM " + tablename;
        if (!ObjectUtils.isEmpty(condition)) {
            sql += " WHERE " + condition;
        }
        return queryParseInt(dataSourceId, sql, params);
    }

    /**
     * 生成新WID
     *
     * @return String
     * @throws Exception
     */
    public String getNewWid() throws Exception {
        return IdUtil.simpleUUID();
    }

    /**
     * 获取数据库类型
     *
     * @param dataSourceId 数据源
     * @return String
     * @throws Exception
     */
    @Cacheable(cacheNames = "orange-cache", keyGenerator = "orangeCacheKeyGenerator", cacheResolver = "orangeCacheResolver")
    public String getDatabaseProductName(String dataSourceId) throws Exception {
        if (dataSourceId == null || "".equals(dataSourceId)) {
            Connection conn = null;
            try {
                conn = this.jdbcTemplate.getDataSource().getConnection();
                DatabaseMetaData md = conn.getMetaData();
                return md.getDatabaseProductName();
            } finally {
                // 此次必须手动关闭连接，否则会出现连接池泄露
                if (conn != null) {
                    conn.close();
                }
            }
        } else {
            Connection conn = null;
            try {
                conn = getJdbcTemplate(dataSourceId).getDataSource().getConnection();
                DatabaseMetaData md = conn.getMetaData();
                return md.getDatabaseProductName();
            } finally {
                // 此次必须手动关闭连接，否则会出现连接池泄露
                if (conn != null) {
                    conn.close();
                }
            }
        }
    }

    /**
     * 获取表元素信息
     *
     * @param dataSourceId 数据源
     * @param tableName 表名称
     * @return TableMetaData
     * @throws Exception
     */
    @Cacheable(cacheNames = "orange-cache", keyGenerator = "orangeCacheKeyGenerator", cacheResolver = "orangeCacheResolver")
    public TableMetaData getTableMeta(String dataSourceId, String tableName) throws Exception {
        if (ObjectUtils.isEmpty(dataSourceId)) {
            dataSourceId = DynamicDataSourceContextHolder.getDataSourceId();
        }
        Connection conn = DbUtil.getDaoSevice().getJdbcTemplate(dataSourceId).getDataSource().getConnection();
        try {
            DatabaseMetaData dbMetaData = conn.getMetaData();
            // 主键
            String primaryKey = getPrimaryKey(dataSourceId, dbMetaData, tableName);
            // 索引
            List<TableIndexInfo> index = getIndexInfo(dataSourceId, dbMetaData, tableName, primaryKey);
            // 列信息
            Map<String, ColumnMetaData> columnMap = getColumn(dataSourceId, dbMetaData, tableName);

            TableMetaData tableMetaData = new TableMetaData(tableName, null, primaryKey, index, columnMap);
            return tableMetaData;
        } catch (Exception e) {
            throw new Exception(e);
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
    }

    /**
     * 获取表的主键名称
     *
     * @param dataSourceId 数据源
     * @param dbMetaData dbMetaData
     * @param tableName 表名称
     * @return String
     * @throws Exception
     */
    private String getPrimaryKey(String dataSourceId, DatabaseMetaData dbMetaData, String tableName) throws Exception {
        ResultSet resultSet = null;
        try {
            String catalog = null;
            String schemaPattern = getSchemaPattern(dataSourceId, dbMetaData);
            resultSet = dbMetaData.getPrimaryKeys(catalog, schemaPattern, tableName);
            List<String> primaryKey = new ArrayList<>();
            while (resultSet.next()) {
                primaryKey.add(resultSet.getString("COLUMN_NAME"));
            }
            if (ObjectUtils.isEmpty(primaryKey)) {
                primaryKey.add("WID");
            }
            return ArrayUtils.toString(String.join(",", primaryKey));
        } catch (Exception e) {
            throw new Exception(e);
        } finally {
            if (resultSet != null) {
                resultSet.close();
            }
        }
    }

    /**
     * 获取索引
     *
     * @param dataSourceId 数据源
     * @param dbMetaData DatabaseMetaData
     * @param tableName 表名称
     * @param primaryKey 主键
     * @return List
     * @throws Exception
     */
    private List<TableIndexInfo> getIndexInfo(String dataSourceId, DatabaseMetaData dbMetaData, String tableName,
        String primaryKey) throws Exception {
        List<TableIndexInfo> index = new ArrayList<TableIndexInfo>();
        ResultSet resultSet = null;
        try {
            String catalog = null;
            String schemaPattern = getSchemaPattern(dataSourceId, dbMetaData);
            resultSet = dbMetaData.getIndexInfo(catalog, schemaPattern, tableName, false, false);
            while (resultSet.next()) {
                String indexName = resultSet.getString("INDEX_NAME");
                String columnName = resultSet.getString("COLUMN_NAME");
                boolean nonUnique = resultSet.getBoolean("NON_UNIQUE");
                if (primaryKey != null && primaryKey.equals(columnName)) {
                    continue;
                }
                TableIndexInfo tableIndexInfo = new TableIndexInfo(indexName, columnName, nonUnique);
                index.add(tableIndexInfo);
            }
            return index;
        } catch (Exception e) {
            throw new Exception(e);
        } finally {
            if (resultSet != null) {
                resultSet.close();
            }
        }

    }

    /**
     * 获取表中列信息
     *
     * @param dataSourceId 数据源 
     * @param dbMetaData DatabaseMetaData
     * @param tableName 表名称
     * @return Map
     * @throws Exception
     */
    private Map<String, ColumnMetaData> getColumn(String dataSourceId, DatabaseMetaData dbMetaData, String tableName)
        throws Exception {
        Map<String, ColumnMetaData> columnMap = new LinkedHashMap<String, ColumnMetaData>();
        SqlRowSet rowSet = getJdbcTemplate(dataSourceId).queryForRowSet("SELECT * FROM " + tableName + " WHERE 1=0");
        SqlRowSetMetaData metaData = rowSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        String catalog = null;
        String schemaPattern = getSchemaPattern(dataSourceId, dbMetaData);
        ResultSet resultSet = null;
        try {
            resultSet = dbMetaData.getColumns(catalog, schemaPattern, tableName, null);
            while (resultSet.next()) {
                String columnName = resultSet.getString("COLUMN_NAME");
                int columnSize = resultSet.getInt("COLUMN_SIZE");
                String remarks = resultSet.getString("REMARKS");
                int dataType = resultSet.getInt("DATA_TYPE");
                resultSet.getString("TYPE_NAME");
                boolean nullable;
                if ("NO".equals(resultSet.getString("IS_NULLABLE"))) {
                    nullable = false;
                } else {
                    nullable = true;
                }
                int precision = 0;
                int scale = 0;
                String columnClassName = null;
                String type = null;
                for (int i = 1; i <= columnCount; i++) {
                    String name = metaData.getColumnName(i);
                    if (columnName.equals(name)) {
                        precision = metaData.getPrecision(i);
                        scale = metaData.getScale(i);
                        columnClassName = metaData.getColumnClassName(i);
                        if ("java.math.BigDecimal".equals(columnClassName)) {
                            columnClassName = "java.lang.Long";
                        }
                        int index = columnClassName.lastIndexOf(".");
                        if (index > 0) {
                            type = columnClassName.substring(index + 1);
                        } else {
                            type = columnClassName;
                        }
                        break;
                    }
                }
                ColumnMetaData columnMetaData = new ColumnMetaData(columnName, columnSize, columnClassName, type,
                    remarks, dataType, nullable, precision, scale);
                columnMap.put(columnName, columnMetaData);
            }
            return columnMap;
        } catch (Exception e) {
            throw new Exception(e);
        } finally {
            if (resultSet != null) {
                resultSet.close();
            }
        }
    }

    /**
     * 获取SchemaPattern
     *
     * @param dataSourceId 数据源
     * @param dbMetaData DatabaseMetaData
     * @return String
     * @throws Exception
     */
    private String getSchemaPattern(String dataSourceId, DatabaseMetaData dbMetaData) throws Exception {
        String schemaPattern = null;
        String type = getDatabaseProductName(dataSourceId);
        if (DbTypeEnum.ORACLE.toString().equalsIgnoreCase(type)) {
            schemaPattern = dbMetaData.getUserName();
        }
        return schemaPattern;
    }

    /**
     * 校验集合元素是否相等
     *
     * @param datas 数据
     * @throws Exception
     */
    private void checkListElementEqual(List<Map<String, Object>> datas) throws Exception {
        int size = datas.size();
        if (size > 1) {
            Set<String> set0 = (datas.get(0)).keySet();
            for (int i = 1; i < size; i++) {
                if (!isEqualSet(set0, (datas.get(i)).keySet())) {
                    LOGGER.error("set0:" + datas.get(0).toString());
                    LOGGER.error("set" + i + ":" + datas.get(i).toString());
                    throw new Exception("集合中的元素结构不一致,无法批量保存");
                }
            }
        }
    }

    /**
     * 比较元素是否一致
     *
     * @param set1 第一个元素
     * @param set2 第二个元素
     * @return boolean
     */
    @SuppressWarnings({ "rawtypes", "unchecked" })
    private boolean isEqualSet(final Collection set1, final Collection set2) {
        if (set1 == set2) {
            return true;
        }
        if (set1 == null || set2 == null || set1.size() != set2.size()) {
            return false;
        }

        return set1.containsAll(set2);
    }

    /**
     * 返回占位符
     *
     * @param list 参数列表
     * @return String
     * @throws Exception
     */
    private String getSqlPlaceholder(List<?> list) throws Exception {
        StringBuilder sb = new StringBuilder();
        for (int i = 0, len = list.size(); i < len; i++) {
            if (i == 0) {
                sb.append("?");
            } else {
                sb.append(",?");
            }
        }
        return sb.toString();
    }

    /**
     * 获取指定数据源事务状态
     *
     * @param dataSourceId 数据源
     * @return PomeloTransactionStatus
     * @throws TransactionException
     */
    public PomeloTransactionStatus getTransaction(String dataSourceId) throws TransactionException {
        return getTransaction(dataSourceId, null);
    }

    /**
     * 获取事务
     *
     * @param dataSourceId 数据源
     * @param definition DefaultTransactionDefinition
     * @return PomeloTransactionStatus
     * @throws TransactionException
     */
    public PomeloTransactionStatus getTransaction(String dataSourceId, DefaultTransactionDefinition definition)
        throws TransactionException {
        if (dataSourceId == null || "".equals(dataSourceId)) {
            dataSourceId = DynamicDataSourceContextHolder.getDataSourceId();
        }
        String beanId = null;
        if (dataSourceId == null || "".equals(dataSourceId)
            || !DynamicDataSourceContextHolder.containsDataSourceId(dataSourceId)) {
            beanId = "PlatformTransactionManager-defaultDataSource";
        } else {
            beanId = "PlatformTransactionManager-" + dataSourceId;
        }
        if (definition == null) {
            definition = new DefaultTransactionDefinition();
        }
        DefaultTransactionStatus transactionStatus = (DefaultTransactionStatus) MultiDataSourceUtil
            .getPlatformTransactionManager(beanId).getTransaction(definition);
        PomeloTransactionStatus pomeloTransactionStatus = new PomeloTransactionStatus(
            transactionStatus.getTransaction(), transactionStatus.isNewTransaction(),
            transactionStatus.isNewSynchronization(), transactionStatus.isReadOnly(), transactionStatus.isDebug(),
            transactionStatus.getSuspendedResources());
        pomeloTransactionStatus.setPlatformTransactionManagerBeanId(beanId);
        return pomeloTransactionStatus;
    }

    /**
     * 提交事务
     *
     * @param status 事务状态
     * @throws TransactionException
     */
    public void commit(PomeloTransactionStatus status) throws TransactionException {
        MultiDataSourceUtil.getPlatformTransactionManager(status.getPlatformTransactionManagerBeanId()).commit(status);
    }

    /**
     * 回滚事务
     *
     * @param status 事务状态
     * @throws TransactionException
     */
    public void rollback(PomeloTransactionStatus status) throws TransactionException {
        MultiDataSourceUtil.getPlatformTransactionManager(status.getPlatformTransactionManagerBeanId())
            .rollback(status);
    }

    /**
     * 修改数据类型
     *
     * @param <T> T
     * @param datas 数据
     * @throws Exception
     */
    @SuppressWarnings("unchecked")
    public <T> void changeType(List<T> datas) throws Exception {
        if (datas == null || datas.isEmpty()) {
            return;
        }
        for (Object data : datas) {
            if (data instanceof Map) {
                Map<String, Object> map = (Map<String, Object>) data;
                Iterator<Entry<String, Object>> iter = map.entrySet().iterator();
                while (iter.hasNext()) {
                    Entry<String, Object> entry = iter.next();
                    String key = entry.getKey();
                    Object value = entry.getValue();
                    if (value instanceof Clob) {
                        Clob clob = (Clob) value;
                        String clobString = clobToString(clob);
                        map.put(key, clobString);
                    } else if (value instanceof Timestamp) {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        map.put(key, sdf.format(value));
                    } else if (value instanceof Date) {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        map.put(key, sdf.format(value));
                    }
                }
            }

        }
    }

    /**
     * 类型转换
     *
     * @param clob Clob
     * @return String
     * @throws Exception
     */
    private String clobToString(Clob clob) throws Exception {
        String detailinfo = "";
        if (clob != null) {
            detailinfo = clob.getSubString((long) 1, (int) clob.length());
        }
        return detailinfo;
    }

    /**
     * 打印日志
     *
     * @param sw SqlWrapper
     * @throws Exception
     */
    private void logSql(SqlWrapper sw) throws Exception {
        LOGGER.debug("^^_^^sql:" + sw.getSql());
        if (sw.getParams() != null && !sw.getParams().isEmpty()) {
            for (Object param : sw.getParams()) {
                if (param instanceof Blob || param instanceof ByteArrayOutputStream || param instanceof OutputStream) {
                    continue;
                }
                LOGGER.debug("^^_^^param:" + param);
            }
        }
    }

    /**
     * 获取所有表信息
     *
     * @param dataSourceId 数据源
     * @return List
     * @throws Exception
     */
    @Cacheable(cacheNames = "orange-cache", keyGenerator = "orangeCacheKeyGenerator", cacheResolver = "orangeCacheResolver")
    public List<Map<String, Object>> getTables(String dataSourceId) throws Exception {
        Connection conn = DbUtil.getDaoSevice().getJdbcTemplate(dataSourceId).getDataSource().getConnection();
        List<Map<String, Object>> tables = new ArrayList<Map<String, Object>>();
        try {
            DatabaseMetaData dbMetaData = conn.getMetaData();
            String catalog = null;
            String schemaPattern = null;
            if ("oracle".equalsIgnoreCase(DbUtil.getDatabaseProductName(dataSourceId))) {
                schemaPattern = dbMetaData.getUserName();
            }
            ResultSet resultSet = dbMetaData.getTables(catalog, schemaPattern, null, new String[] { "TABLE" });
            while (resultSet.next()) {
                String tableName = resultSet.getString("TABLE_NAME").toUpperCase();
                Map<String, Object> data = new HashMap<String, Object>();
                data.put("TABLE_NAME", tableName);
                data.put("REMARKS", resultSet.getString("REMARKS"));
                tables.add(data);
            }
            return tables;
        } finally {
            conn.close();
        }
    }

    /**
     * 表是否存在
     *
     * @param dataSourceId 数据源
     * @param tableName 表名称
     * @return boolean
     */
    public boolean tableExists(String dataSourceId, String tableName) {
        try {
            String sql = "SELECT * FROM " + tableName + " WHERE 1=2";
            execute(dataSourceId, sql, new Object[] {});
            return true;
        } catch (Exception e) {
            return false;
        }
    }
}

/**
 * 行数据封装
 *
 * @author biankudingcha
 * @date: 2021-07-23
 * @since 0.5.0
 */
class PomeloColumnMapRowMapper implements RowMapper<Map<String, Object>> {

    @Override
    public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        Map<String, Object> mapOfColValues = createColumnMap(columnCount);
        for (int i = 1; i <= columnCount; i++) {
            String key = getColumnKey(JdbcUtils.lookupColumnName(rsmd, i));
            Object obj = getColumnValue(rs, i);
            mapOfColValues.put(key.toUpperCase(), obj);
        }
        return mapOfColValues;
    }

    /**
     * 转换map
     *
     * @param columnCount 列数量
     * @return Map
     */
    protected Map<String, Object> createColumnMap(int columnCount) {
        return new LinkedCaseInsensitiveMap<Object>(columnCount);
    }

    /**
     * 获取列名称
     *
     * @param columnName 列名称
     * @return String
     */
    protected String getColumnKey(String columnName) {
        return columnName;
    }

    /**
     * 获取值
     *
     * @param rs rs
     * @param index index
     * @return Object
     * @throws SQLException
     */
    protected Object getColumnValue(ResultSet rs, int index) throws SQLException {
        return JdbcUtils.getResultSetValue(rs, index);
    }

}

/**
 * sql包装器
 *
 * @author biankudingcha
 * @date: 2021-07-23
 * @since 0.5.0
 */
class SqlWrapper {

    /**
     * sql语句
     */
    private String sql;

    /**
     * 参数
     */
    private List<Object> params = new ArrayList<Object>();

    public SqlWrapper(String sql, List<Object> params) {
        this.sql = sql;
        this.params = params;
    }

    /**
     * 获取SQL
     *
     * @return String
     */
    public String getSql() {
        return sql;
    }

    /**
     * 设置SQL
     *
     * @param sql SQL
     */
    public void setSql(String sql) {
        this.sql = sql;
    }

    /**
     * 获取参数
     *
     * @return List
     */
    public List<Object> getParams() {
        return params;
    }

    /**
     * 设置参数
     *
     * @param params 参数
     */
    public void setParams(List<Object> params) {
        this.params = params;
    }

}